package com.scau.ums.util.dao;

import com.scau.ums.model.Suggestion;
import com.scau.ums.service.CommonSuggest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class SelectAllSuggestion {

    /**
     * 查询全部
     * 
     */

    private String driverClassName="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://47.102.199.9:3306/ums?serverTimezone=GMT";
    private String username="root";
    private String password="linux";
    public List<Suggestion> find(){
        SelectByPageSql selectByPageSql=new SelectByPageSql();
        try {
            //加载驱动
            Class.forName(driverClassName);
            //获取数据库连接信息
            Connection con = DriverManager.getConnection(url, username,password);
            //执行sql
            List<Suggestion> list=new ArrayList<Suggestion>();
            String sql=selectByPageSql.selectRowByPageSetOrderByClause
                    ("suggestion","date DESC",0,10,"state='通过' and isTop="+CommonSuggest.ISTOP_TURE);
            PreparedStatement ps = con.prepareStatement(sql);
            //返回查询的结果集
            ResultSet rs = ps.executeQuery();
            //遍历结果集
            while(rs.next()){
                String speakerName = rs.getString("speakerName");
                String content = rs.getString("content");
                int id = rs.getInt("id");
                String speakerId = rs.getString("speakerId");
                Date date=rs.getDate("date");
                String speakerType=rs.getString("speakerType");
                String state=rs.getString("state");
                int isTop=rs.getInt("isTop");
                //封装结果到对象实体
                Suggestion s=new Suggestion(id,speakerId,speakerName,content,date,speakerType,state,isTop);
                //将结果放入list集合
                list.add(s);
            }
            sql=selectByPageSql.selectRowByPageSetOrderByClause
                    ("suggestion","date DESC",0,10-list.size(),"state='通过' and isTop="+CommonSuggest.ISTOP_FALSE );
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            //遍历结果集
            while(rs.next()){
                String speakerName = rs.getString("speakerName");
                String content = rs.getString("content");
                int id = rs.getInt("id");
                String speakerId = rs.getString("speakerId");
                Date date=rs.getDate("date");
                String speakerType=rs.getString("speakerType");
                String state=rs.getString("state");
                int isTop=rs.getInt("isTop");
                //封装结果到对象实体
                Suggestion s=new Suggestion(id,speakerId,speakerName,content,date,speakerType,state,isTop);
                //将结果放入list集合
                list.add(s);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
